﻿using Dapper;
using System.Data.SqlClient;
using yunbo.Model.SSM;

namespace yunbo.Repositorys.SSMRepositorys
{
    public class SectionRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";

        public FYSection GetAll(int PageNumber, int PageSize)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$"SELECT * FROM [dbo].[section]
                               ORDER BY  [section_id] 
                               OFFSET ({PageNumber}-1)*{PageSize} ROWS
                               FETCH NEXT {PageSize} ROWS ONLY;";
                List<Section> sections = connection.Query<Section>(sql).ToList();
                string sql1 = "SELECT COUNT(*) FROM [dbo].[section]";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYSection fYSection = new FYSection() 
                {
                    sections = sections,
                    zon = zon,
                };
                return fYSection;
            }
        }
        public int AddSection(Section section)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"insert into [dbo].[section](section_name,section_status, section_phone,section_charge,section_email, section_create_by, section_create_time)\r\nvalues ('{section.section_name}','{section.section_status}','{section.section_phone}','{section.section_charge}','{section.section_email}','{section.section_create_by}','{section.section_create_time}');";
                int se = connection.Execute(sql);
                return se;
            }
        }
        public int UpSection(Section section)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[section] " +
                    $"\r\nset section_name='{section.section_name}', section_phone='{section.section_phone}', section_charge='{section.section_charge}', section_email='{section.section_email}',[section_update_by]='{section.section_update_by}',[section_update_time]='{section.section_update_time}'\r\nwhere [section_id]='{section.section_id}'";
                int section1 = connection.Execute(sql);
                return section1;
            }
        }
        public int DeSection(int id)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"delete from [dbo].[section]\r\nwhere [section_id]={id}";
                int se3 = connection.Execute(sql);
                return se3;
            }

        }
        public int Upstatus(int id, int status)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[section] set [section_status]='{status}'\r\nwhere [section_id]='{id}'";
                int status1 = connection.Execute(sql);
                return status1;
            }
        }
        public FYSection MHCX(int PageNumber, int PageSize, int? status, string? sname)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"SELECT * FROM [dbo].[section] WHERE section_status like '%{status}%' and section_name like '%{sname}%'\r\nORDER BY  [section_id] \r\nOFFSET ({PageNumber}-1)*{PageSize} ROWS\r\nFETCH NEXT {PageSize} ROWS ONLY;";
                List<Section> sections = connection.Query<Section>(sql).ToList();
                string sql1 = $"SELECT COUNT(*) FROM [dbo].[section] WHERE section_status like '%{status}%' and section_name like '%{sname}%'";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYSection fYSection = new FYSection()
                {
                    sections = sections,
                    zon = zon,
                };
                return fYSection;
            }
        }
    }
}
